ARTIVA RM FILE FORMATS

child pages:

page index:
File Formats
File Format Sections
File Format Section Detail
File Formats from FPRs    
find all scripts that are directly called by placement file formats
"System" table must be used in General Section Information

tables FSVEIMPFF & FSRUNSCHED have no data on production server

File Formats

TABLE NAME:        FSFILEFORMAT
TABLE DESC:         "File Format Section"
ELEMENT TYPE:   

-- THIS IS THE MAIN FILE FORMAT RECORD
SELECT
FSFFDESC ,
FSFFID,
FSFFPOSTSCRIPT,
*
FROM
FSFILEFORMAT
WHERE
/*FSFFDESC LIKE "%[first chars of client name]%"*/
FSFFID = "ZZX[clientname]RE" -- FILE FORMAT NAME

File Format Sections

One section of a file format

TABLE NAME:        FSFFSECTION
TABLE DESC:         "File Format Section"
ELEMENT TYPE:   

-- I think FSFFSFFID is the join to FILE FORMAT FSFILEFORMAT.FSFFID
-- I think FSSFFSID is the join to FSFFSECDET.FSFFSDFFSID

SELECT
*
FROM FSFFSECTION
WHERE
FSFFSFFID LIKE "%ZZX[clientname]RE%"

File Format Section Detail

Details on one section of a file format

TABLE NAME:        FSFFSECDET
TABLE DESC:         "File Format Section Detail"
ELEMENT TYPE:   

-- I THINK FSFFSDRFID IS "DETAIL RECORD FORMAT" EDIT BOX
SELECT FSFFSDRFID
,FSFFSDFFSID
, *
FROM FSFFSECDET
WHERE
FSFFSDRFID LIKE "%ZX[first chars of clientid]RE%"



SELECT
*
FROM FSFFSECDET
WHERE FSFFSDRFSCRIPT LIKE "%SLM%" -- RECORD SCRIPT
ORDER BY FSFFSDRFID -- note one of the FSFFSDRECID ("RECORD ID") is from [remainder missing]



-- FIND ALL EXPORT RECORD SCRIPTS
SELECT
DISTINCT(FSFFSDRFSCRIPT) AS RECORD_SCRIPT
FROM FSFFSECDET
WHERE FSFFSDRFSCRIPT LIKE "ZZX%"
GROUP BY FSFFSDRFSCRIPT

File Formats from FPRS

This is the file format from file process run where email lists are set

TABLE NAME:        FSSYSFILEFORMATS
TABLE DESC:         "System Default File Formats"
ELEMENT TYPE:   

SELECT * FROM FSSYSFILEFORMATS

-- FIND THE NUMBER OF EACH UNIQUE Email List for Successful File
SELECT
COUNT(FSSYSFFID)
,FSSYSFFEMLISTSUC
FROM FSSYSFILEFORMATS
WHERE FSSYSFFID LIKE "%ADV%"
GROUP BY FSSYSFFEMLISTSUC

-- FIND THE NUMBER OF EACH UNIQUE Email List for UNSuccessful File
SELECT
COUNT(FSSYSFFID)
,FSSYSFFEMLISTUNSUC
FROM FSSYSFILEFORMATS
GROUP BY FSSYSFFEMLISTUNSUC


find all scripts that are directly called by placement file formats

SELECT
FSFILEFORMAT.FSFFID
,FSFILEFORMAT.FSFFDESC AS DESCRIPTION
,FSFILEFORMAT.FSFFHDRSCRIPT AS FILE_FORMAT_HEADER_SCR
,FSFILEFORMAT.FSFFTRLSCRIPT AS FILE_FORMAT_TRAILER_SCR
,FSFILEFORMAT.FSFFPRESCRIPT AS PRE_FILE_FORMAT_SCR
,FSFILEFORMAT.FSFFPOSTSCRIPT AS POST_FILE_FORMAT_SCR
,FSFILEFORMAT.FSFFHDRSCRIPT AS FILE_FORMAT_HEADER_SCR
/*,FSFFSECTION.FSFFSFFID AS FILE_FORMAT*/
,FSFFSECTION.FSFFSGRPPRESCRIPT AS GROUP_PRE_SCRIPT
,FSFFSECTION.FSFFSGRPPOSTSCRIPT AS GROUP_POST_SCRIPT
,FSFFSECTION.FSFFSHDRSCRIPT AS   File_Format_Section_Header_Scr
,FSFFSECTION.FSFFSTRLSCRIPT AS File_Format_Section_Trailer_Scr
,FSFFSECTION.FSFFSPRETRANSCRIPT AS pre_trans
,FSFFSECTION.FSFFSPSTTRANSCRIPT AS post_trans
,FSFFSECTION.FSFFSPRESCRIPT AS pre_section
,FSFFSECTION.FSFFSPOSTSCRIPT AS post_section
/*
,FSFFSDFFSID  -- File Format Section ID
,FSFFSDID  --File Format Section Detail ID
*/
, FSFFSDPRESCRIPT AS PRE_SECTION
,FSFFSDPOSTSCRIPT AS POST_SECTION
,FSFFSDRFSCRIPT AS RECORD_SCRIPT
,FSFFSDPRESCRIPT AS PRE_RECORD
,FSFFSDRFPOSTSCRIPT AS POST_RECORD
FROM FSFFSECDET
JOIN FSFFSECTION ON FSFFSECTION.FSFFSID = FSFFSECDET.FSFFSDFFSID
JOIN FSFILEFORMAT ON FSFILEFORMAT.FSFFID = FSFFSECTION.FSFFSFFID
WHERE
FSFFSDFFSID IN /*File Format Section ID*/
         (
         -- FIND ALL FILE FORMAT SECTIONS' KEY VALUES WHERE THE FILE FORMAT CONTAINING IT IS A PLACEMENT (SINCE THE NAME ENDS IN "PL")
         SELECT
         /*FSFFSFFID AS FILE_FORMAT_ID,
         FSFFSPSTTRANSCRIPT,
         FSFFSPOSTSCRIPT , */
         FSFFSID --AS FILE_FORMAT_SECTION_ID
         FROM FSFFSECTION
         WHERE FSFFSFFID IN (SELECT FSFFID FROM FSFILEFORMAT WHERE (FSFFID like "%PL"))
         )

"System" table must be used in General Section Information

2022-05-24 I created an export that had to do a pre-pass on the import file.   All variables used in the record format if the pre-pass was successful were shared variables (as opposed to having a field from a table). Choosing ARACCOUNT in the General Secion Information's Details area caused the record script to not be called. The solution was to remove the entry (as the table couldn't be changed in the workstation interface) and adding a new row that used System table, then having to add all the shared variables to the Record Format Description again.

last updated:    Wed 2022-07-20 6:38 AM